package holo.Utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.fastjson.JSONObject;
import util.YtJSONObject;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @author Malegod_xiaofei
 * @create 2022-06-10-11:55
 */
public class Update_Holo_2020 {
    //定义SQL 链接 预编译器
    private static DataSource dataSource = null;
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;

    public static void main(String[] args) throws Exception {
        String[] month = {"202001", "202002", "202003", "202004", "202005", "202006", "202007", "202008", "202009", "202010", "202011", "202012"};
        Map<String, JSONObject> check_region_id = new HashMap<>();
        check_region_id.put("140211", new YtJSONObject().put("new_region_id", "140214").put("province", "山西省").put("city", "大同市").put("district", "云冈区").getHome());
        check_region_id.put("140411", new YtJSONObject().put("new_region_id", "140403").put("province", "山西省").put("city", "长治市").put("district", "潞州区").getHome());
        check_region_id.put("232723", new YtJSONObject().put("new_region_id", "232701").put("province", "黑龙江省").put("city", "大兴安岭地区").put("district", "漠河市").getHome());
        check_region_id.put("370125", new YtJSONObject().put("new_region_id", "370115").put("province", "山东省").put("city", "济南市").put("district", "济阳区").getHome());
        check_region_id.put("371626", new YtJSONObject().put("new_region_id", "371681").put("province", "山东省").put("city", "滨州市").put("district", "邹平市").getHome());
        check_region_id.put("441902", new YtJSONObject().put("new_region_id", "441931").put("province", "广东省").put("city", "东莞市").put("district", "南城街道").getHome());
        check_region_id.put("441904", new YtJSONObject().put("new_region_id", "441935").put("province", "广东省").put("city", "东莞市").put("district", "东城街道").getHome());
        check_region_id.put("441906", new YtJSONObject().put("new_region_id", "441917").put("province", "广东省").put("city", "东莞市").put("district", "万江街道").getHome());
        check_region_id.put("441911", new YtJSONObject().put("new_region_id", "441936").put("province", "广东省").put("city", "东莞市").put("district", "莞城街道").getHome());
        check_region_id.put("450331", new YtJSONObject().put("new_region_id", "450381").put("province", "广西壮族自治区").put("city", "桂林市").put("district", "荔浦市").getHome());
        check_region_id.put("451003", new YtJSONObject().put("new_region_id", "451021").put("province", "广西壮族自治区").put("city", "百色市").put("district", "田阳区").getHome());
        check_region_id.put("230702", new YtJSONObject().put("new_region_id", "230717").put("province", "黑龙江省").put("city", "伊春市").put("district", "伊美区").getHome());
        check_region_id.put("230703", new YtJSONObject().put("new_region_id", "230726").put("province", "黑龙江省").put("city", "伊春市").put("district", "南岔县").getHome());
        check_region_id.put("230707", new YtJSONObject().put("new_region_id", "230724").put("province", "黑龙江省").put("city", "伊春市").put("district", "丰林县").getHome());
        check_region_id.put("341822", new YtJSONObject().put("new_region_id", "341882").put("province", "安徽省").put("city", "宣城市").put("district", "广德市").getHome());
        check_region_id.put("361121", new YtJSONObject().put("new_region_id", "361104").put("province", "江西省").put("city", "上饶市").put("district", "广信区").getHome());
        check_region_id.put("411626", new YtJSONObject().put("new_region_id", "411603").put("province", "河南省").put("city", "周口市").put("district", "淮阳区").getHome());
        check_region_id.put("421023", new YtJSONObject().put("new_region_id", "421088").put("province", "湖北省").put("city", "荆州市").put("district", "监利市").getHome());
        check_region_id.put("430521", new YtJSONObject().put("new_region_id", "430582").put("province", "湖南省").put("city", "邵阳市").put("district", "邵东市").getHome());
        check_region_id.put("510922", new YtJSONObject().put("new_region_id", "510981").put("province", "四川省").put("city", "遂宁市").put("district", "射洪市").getHome());
        check_region_id.put("130223", new YtJSONObject().put("new_region_id", "130284").put("province", "河北省").put("city", "唐山市").put("district", "滦州市").getHome());
        check_region_id.put("230705", new YtJSONObject().put("new_region_id", "230751").put("province", "黑龙江省").put("city", "伊春市").put("district", "金林区").getHome());
        check_region_id.put("230706", new YtJSONObject().put("new_region_id", "230718").put("province", "黑龙江省").put("city", "伊春市").put("district", "乌翠区").getHome());
        check_region_id.put("230708", new YtJSONObject().put("new_region_id", "230717").put("province", "黑龙江省").put("city", "伊春市").put("district", "伊美区").getHome());
        check_region_id.put("230709", new YtJSONObject().put("new_region_id", "230751").put("province", "黑龙江省").put("city", "伊春市").put("district", "金林区").getHome());
        check_region_id.put("230710", new YtJSONObject().put("new_region_id", "230724").put("province", "黑龙江省").put("city", "伊春市").put("district", "丰林县").getHome());
        check_region_id.put("230711", new YtJSONObject().put("new_region_id", "230717").put("province", "黑龙江省").put("city", "伊春市").put("district", "伊美区").getHome());
        check_region_id.put("230712", new YtJSONObject().put("new_region_id", "230723").put("province", "黑龙江省").put("city", "伊春市").put("district", "汤旺县").getHome());
        check_region_id.put("230713", new YtJSONObject().put("new_region_id", "230725").put("province", "黑龙江省").put("city", "伊春市").put("district", "大箐山县").getHome());
        check_region_id.put("230715", new YtJSONObject().put("new_region_id", "230724").put("province", "黑龙江省").put("city", "伊春市").put("district", "丰林县").getHome());
        check_region_id.put("230716", new YtJSONObject().put("new_region_id", "230719").put("province", "黑龙江省").put("city", "伊春市").put("district", "友好区").getHome());
        check_region_id.put("371200", new YtJSONObject().put("new_region_id", "370100").put("province", "山东省").put("city", "济南市").put("district", "").getHome());
        check_region_id.put("371202", new YtJSONObject().put("new_region_id", "370116").put("province", "山东省").put("city", "济南市").put("district", "莱芜区").getHome());
        check_region_id.put("371203", new YtJSONObject().put("new_region_id", "370117").put("province", "山东省").put("city", "济南市").put("district", "钢城区").getHome());
        check_region_id.put("542400", new YtJSONObject().put("new_region_id", "540600").put("province", "西藏自治区").put("city", "那曲市").put("district", "").getHome());

        String sql = "";
        for (int i = 0; i < month.length; i++) {

            for (Map.Entry<String, JSONObject> s : check_region_id.entrySet()) {
                System.out.println("月份 : " + month[i]);
//                sql = "update dbadmin.o2o_region_category_app_tui_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where region_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_showlive_app_" + month[i] + " set regional_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where regional_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_tourism_idl_tui_" + month[i] + " set region_id_out = '" + s.getValue().getString("new_region_id") + "',province_out = '" + s.getValue().getString("province") + "',city_out = '" + s.getValue().getString("city") + "',district_out = '" + s.getValue().getString("district") + "' where region_id_out = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_allplat_shop_bdl_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "' where region_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_anchor_app_" + month[i] + " set regional_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where regional_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
                sql = "update dbadmin.o2o_region_category_idl_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where region_id = '" + s.getKey() + "';";
                System.out.println("更新 sql :" + sql);
                update(sql);
            }
        }

    }

    public static void select(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        // 打印 resultSet 数据
        ResultSetMetaData resultSetmd = resultSet.getMetaData();
        int columnsNumber = resultSetmd.getColumnCount();
        while (resultSet.next()) {
            for (int i = 1; i <= columnsNumber; i++) {
                if (i > 1) System.out.print(",  ");
                String columnValue = resultSet.getString(i);
                System.out.print(columnValue + " " + resultSetmd.getColumnName(i));
            }
            System.out.println("");
        }
        preparedStatement.close();
    }

    public static void update(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();
        // 打印 resultSet 数据
        System.out.println("一共 : " + resultSet + " 条数据受影响 ! ");
        preparedStatement.close();

    }


}